**Purpose: Clean Tabulated Areas for MapBiomas Land Cover in Grid Cells 

*Program: Clean raw tabulations
capture program drop MapBiomas_clean
	program MapBiomas_clean
			
		*Import Tabulated Area Data 
		import delimited "$inputs\Tabulated Areas/`2'_Grid_DEF_`1'.csv", clear
			
		*Clean variables 
		gen state = "`2'"
		drop oid_
		rename value_* value*
		rename pagename cell
				
		*Reshape long
		reshape long value, i(cell) j(DEF_CODE)
		rename value y`1'
		
		save "$outputs\Tabulated Areas/`2'_`1'.dta", replace 
	end
	
*Run program for all states, years
foreach state in Bahia EspiritoSanto MinasGerais_Grid_BOTTOM MinasGerais_Grid_TOP Parana RioDeJaneiro SaoPaulo {
	foreach year in 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 {
		MapBiomas_clean `state' `year'
		}
	}
	
*Merge cleaned annual tabulations for each state
foreach state in Bahia EspiritoSanto MinasGerais_Grid_BOTTOM MinasGerais_Grid_TOP Parana RioDeJaneiro SaoPaulo {
	use "$outputs\Tabulated Areas/`state'_2000.dta", clear
	foreach v in 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 {
		merge m:1 cell DEF_CODE using "$outputs\Tabulated Areas/`state'_`v'.dta"
		drop _merge
		}
	save "$outputs\Tabulated Areas/`state'_combined.dta", replace
	}

*Program: Merge MapBiomas Legends and Create Outcomes
capture program drop Merge
program Merge
	
	*Import Cleaned Annual Tabulations
	use "$outputs\Tabulated Areas/`1'_combined.dta", clear

	*Merge in MapBiomas Legends
	preserve
		import excel "$data\MapBiomas\Legends\DEF_Legend.xlsx", firstrow clear
		tempfile DEF_Code
		save `DEF_Code.dta'
	restore

	preserve
		import excel "$data\MapBiomas\Legends\LC_Legend.xlsx", firstrow clear
		tempfile Col6_Code
		save `LC_Code.dta'
	restore

	*Merge in the DEF Legend
	tostring DEF_CODE, replace
	gen DEF_Legend = substr(DEF_CODE,1,1)
	destring DEF_Legend, replace
	merge m:1 DEF_Legend using `DEF_Code.dta'
		drop _merge
		
	*Merge in the Collection 6 Legend 
	gen Col6_Legend = substr(DEF_CODE,2,2)
	destring Col6_Legend, replace
	merge m:1 Col6_Legend using `LC_Code.dta'
		drop if _merge==2
		drop _merge Col6_NamePO
	drop DEF_Legend Col6_Legend
	
	*Reorder and Sort
	order cell state* DEF_CODE DEF_Type Type Col6_Name Col6_Index
	sort cell DEF_CODE
	
	*Define Forest Change
	keep if inlist(DEF_Type,"PV_Suppress","SV_Suppress","SV_Recover")
	keep if Type=="Forest" 
	keep if inlist(Col6_Name,"Forest Formation","Mangrove")
	
	*Collapse to show just the Suppression and Recovery Totals for each cell, and Summarize
	collapse (sum) y*, by(cell state DEF_Type)
	reshape wide y*, i(cell state) j(DEF_Type) string
	rename y*PV_Suppress PV_Suppress*
	rename y*SV_Suppress SV_Suppress*
	rename y*SV_Recover SV_Recover*
	reshape long PV_Suppress SV_Suppress SV_Recover, i(cell state) j(year)
	sort cell state year 

	*Convert square meters to hectares
	replace PV_Suppress = PV_Suppress/10000
	replace SV_Suppress = SV_Suppress/10000
	replace SV_Recover = SV_Recover/10000
	
	*Outcomes 
	gen DEF_Loss = PV_Suppress + SV_Suppress
	gen DEF_Recover = SV_Recover

	*Pre & Post
	gen period = "Pre" if year<2010
		replace period = "Post" if year >= 2010
		
	*Save Output
	save "$outputs\Tabulated Areas/`1'_clean.dta", replace
end 

*Run program for all states
foreach state in Bahia EspiritoSanto MinasGerais_Grid_BOTTOM MinasGerais_Grid_TOP Parana RioDeJaneiro SaoPaulo {
	Merge `state'
	}

*Append states
use "$outputs\Tabulated Areas\Bahia_clean.dta",clear 
append using "$outputs\Tabulated Areas\EspiritoSanto_clean.dta"
append using "$outputs\Tabulated Areas\MinasGerais_Grid_TOP_clean.dta"
append using "$outputs\Tabulated Areas\MinasGerais_Grid_BOTTOM_clean.dta"
append using "$outputs\Tabulated Areas\Parana_clean.dta"
append using "$outputs\Tabulated Areas\RioDeJaneiro_clean.dta"
append using "$outputs\Tabulated Areas\SaoPaulo_clean.dta"

*Save 
save "$outputs\Tabulated Areas\MASTER_Tabulations.dta", replace



